package com.demoweb.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demoweb.model.dto.Board;
import com.demoweb.model.dto.BoardComment;

public class BoardDao {
	
	public void insertBoard(Board board) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			
			conn.setAutoCommit(false);
			
			StringBuilder sql = new StringBuilder(256);
			sql.append("INSERT INTO board ")
			   .append("(gameno, title, writer, content, groupno, step) ")
			   .append("VALUES ")
			   .append("(?, ?, ?, ?, -1, 1)");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, board.getGameNo());
			pstmt.setString(2, board.getTitle());
			pstmt.setString(3, board.getWriter());
			pstmt.setString(4, board.getContent());
			
			pstmt.executeUpdate();
			pstmt.close();
			
			sql.delete(0, sql.length());
			//LAST_INSERT_ID() : 현재 세션에서 마지막으로 처리된 자동 증가 값
			sql.append("UPDATE board ")
			   .append("SET groupno = LAST_INSERT_ID() ")
			   .append("WHERE boardno = LAST_INSERT_ID()");
			pstmt = conn.prepareStatement(sql.toString());
			
			pstmt.executeUpdate();
			
			conn.commit();
			
		} catch (Exception ex) {
			try { conn.rollback(); } catch (Exception ex2) {}
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			try { pstmt.close(); } catch (Exception ex) {}
			try { conn.setAutoCommit(true); } catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) {}
		}
	}
	
///////////////////////////////////////////////////////////////

	public List<Board> getBoardList(int startRow, int count) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;//조회 결과에 접근하는 참조 변수
		//데이터베이스의 데이터를 읽어서 저장할 객체 컬렉션
		ArrayList<Board> boards = new ArrayList<Board>();
		
		try {
			conn = ConnectionHelper.getConnection();
			StringBuilder sql = new StringBuilder(512);
			sql.append("SELECT boardno, title, writer, ")
			   .append("readcount, regdate, deleted, groupno, step, depth ")
			   .append("FROM board ")
			   .append("ORDER BY groupno DESC, step ASC ")
			   .append("LIMIT ?, ?");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, startRow);
			pstmt.setInt(2, count);
			
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				Board board = new Board();
				board.setBoardNo(rs.getInt(1));
				board.setTitle(rs.getString(2));
				board.setWriter(rs.getString(3));
				//board.setContent(rs.getString(4));
				board.setReadCount(rs.getInt(4));
				board.setRegDate(rs.getDate(5));
				board.setDeleted(rs.getBoolean(6));
				board.setGroupNo(rs.getInt(7));
				board.setStep(rs.getInt(8));
				board.setDepth(rs.getInt(9));
				
				boards.add(board);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (rs != null) try { rs.close(); } catch (Exception ex) {}
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
		
		return boards;
	}
	
	public List<Board> getBoardByGameNo(int gameNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;//조회 결과에 접근하는 참조 변수
		//데이터베이스의 데이터를 읽어서 저장할 객체 컬렉션
		ArrayList<Board> boards0 = new ArrayList<Board>();
		
		try {
			conn = ConnectionHelper.getConnection();
			StringBuilder sql = new StringBuilder(512);
			sql.append("SELECT boardno, title, writer, content ")
			.append("From board ")
//			   .append("ORDER BY boardno DESC, ")
			   .append("WHERE gameno = ? ");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, gameNo);
		
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				Board board = new Board();
				board.setBoardNo(rs.getInt(1));
				board.setTitle(rs.getString(2));
				board.setWriter(rs.getString(3));
				board.setContent(rs.getString(4));
				
				
				boards0.add(board);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (rs != null) try { rs.close(); } catch (Exception ex) {}
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
		
		return boards0;
	}

	public Board getBoardByBoardNo(int number) {
		Connection conn = null;
		PreparedStatement pstmt = null, pstmt2 = null;
		ResultSet rs = null, rs2 = null;
		Board board = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			StringBuffer sql = new StringBuffer(300);
			sql.append("SELECT "); 
			sql.append("boardno, title, writer, content, ");
			sql.append("regdate, readcount, ");
			sql.append("deleted, groupno, step, depth ");
			sql.append("FROM board ");
			sql.append("WHERE boardno = ? AND deleted = false");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, number);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				board = new Board();
				board.setBoardNo(rs.getInt(1));
				board.setTitle(rs.getString(2));
				board.setWriter(rs.getString(3));
				board.setContent(rs.getString(4));
				board.setRegDate(rs.getDate(5));
				board.setReadCount(rs.getInt(6));
				board.setDeleted(rs.getBoolean(7));
				board.setGroupNo(rs.getInt(8));
				board.setStep(rs.getInt(9));
				board.setDepth(rs.getInt(10));
				
				//댓글 조회
				sql.delete(0, sql.length());//StringBuilder 비우기
				sql.append("SELECT commentno, boardno, ")
				   .append("	   writer, content, regdate ")
				   .append("FROM boardcomment ")
				   .append("WHERE boardno = ?");
				pstmt2 = conn.prepareStatement(sql.toString());
				pstmt2.setInt(1, number);
				rs2 = pstmt2.executeQuery();
				ArrayList<BoardComment> comments 
					= new ArrayList<BoardComment>();
				while (rs2.next()) {
					BoardComment comment = new BoardComment();
					comment.setCommentNo(rs2.getInt(1));
					comment.setBoardNo(rs2.getInt(2));
					comment.setWriter(rs2.getString(3));
					comment.setContent(rs2.getString(4));
					comment.setRegDate(rs2.getDate(5));
					comments.add(comment);
				}
		
				
			}			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { if (rs2 != null) rs2.close(); } catch (Exception ex) { }
			try { if (pstmt2 != null) pstmt2.close(); } catch (Exception ex) { }
			try { if (rs != null) rs.close(); } catch (Exception ex) { }
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}
		return board;
	}
	
	public int getBoardCount() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;//조회 결과에 접근하는 참조 변수
		int count = 0;
		try {
			conn = ConnectionHelper.getConnection();			
			String sql = "SELECT COUNT(*) FROM board";
			pstmt = conn.prepareStatement(sql);
			
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (rs != null) try { rs.close(); } catch (Exception ex) {}
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
		
		return count;
	}

	public void deleteBoard(int boardNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			
			String sql = 
				"UPDATE board SET deleted = true WHERE boardno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, boardNo);
			
			pstmt.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
	}
	
	public int updateBoard(Board board) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int count = 0;
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"UPDATE board " +
				"SET title = ?, content = ? " + 
				"WHERE boardno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, board.getTitle());
			pstmt.setString(2, board.getContent());
			pstmt.setInt(3, board.getBoardNo());
			count = pstmt.executeUpdate();
		} catch (Exception ex) {
			count = 0;
			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}
		return count;
	}
	
	public int insertReply(Board board) {
		Connection conn = null;		
		PreparedStatement pstmt = null;
		int count = 0;
		try {
			//댓글을 작성할 대상글의 정보를 조회
			Board temp = 
				getBoardByBoardNo(board.getBoardNo());
			
			//영향을 받는 기존 글들의 논리적인 순서 번호 변경
			conn = ConnectionHelper.getConnection();			
			String sql =
				"UPDATE board " + 
				"SET step = step + 1 " + 
				"WHERE step > ? AND groupno = ?";
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, temp.getStep());//step 번호
			pstmt.setInt(2, temp.getGroupNo());//group 번호
			pstmt.executeUpdate();
			pstmt.close();
			
			//댓글 삽입
			StringBuilder sql2 = new StringBuilder(256);
			sql2.append("INSERT INTO board ");
			sql2.append("(title, writer, content, deleted, groupno, step, depth) ");
			sql2.append("VALUES (?, ?, ?, false, ?, ?, ?)");
			pstmt = conn.prepareStatement(sql2.toString());
			pstmt.setString(1, board.getTitle());//제목
			pstmt.setString(2, board.getWriter());//작성자
			pstmt.setString(3, board.getContent());//내용
			pstmt.setInt(4, temp.getGroupNo());//step
			pstmt.setInt(5, temp.getStep() + 1);//step
			pstmt.setInt(6, temp.getDepth() + 1);//depth
			
			count = pstmt.executeUpdate();
		} catch (Exception ex) {
			count = 0;
			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}
		return count;
		
	}
	
	public int insertFlapReply(Board board, int gameno) {
		Connection conn = null;		
		PreparedStatement pstmt = null;
		int count = 0;
		try {
			//댓글을 작성할 대상글의 정보를 조회
			Board temp = 
				getBoardByBoardNo(board.getBoardNo());
			
			//영향을 받는 기존 글들의 논리적인 순서 번호 변경
			conn = ConnectionHelper.getConnection();			
			String sql =
				"UPDATE board " + 
				"SET step = step + 1 " + 
				"WHERE step > ? AND groupno = ?";
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, temp.getStep());//step 번호
			pstmt.setInt(2, temp.getGroupNo());//group 번호
			pstmt.executeUpdate();
			pstmt.close();
			
			//댓글 삽입
			StringBuilder sql2 = new StringBuilder(256);
			sql2.append("INSERT INTO board ");
			sql2.append("(title, writer, content, deleted, groupno, step, depth, gameno) ");
			sql2.append("VALUES (?, ?, ?, false, ?, ?, ?, ?)");
			pstmt = conn.prepareStatement(sql2.toString());
			pstmt.setString(1, board.getTitle());//제목
			pstmt.setString(2, board.getWriter());//작성자
			pstmt.setString(3, board.getContent());//내용
			pstmt.setInt(4, temp.getGroupNo());//step
			pstmt.setInt(5, temp.getStep() + 1);//step
			pstmt.setInt(6, temp.getDepth() + 1);//depth
			pstmt.setInt(7, gameno);
			count = pstmt.executeUpdate();
		} catch (Exception ex) {
			count = 0;
			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}
		return count;
		
	}
	
	public void increaseBoardReadCount(int number) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"UPDATE board " +
				"SET readcount = readcount + 1 " + 
				"WHERE boardno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, number);
			pstmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}
	}
	
	///////////////////////////////////////////////
	
	public void insertComment(BoardComment comment) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			
			String sql = 
				"INSERT INTO boardcomment " +
				"(boardno, writer, content) " +
				"VALUES (?, ?, ?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, comment.getBoardNo());
			pstmt.setString(2, comment.getWriter());
			pstmt.setString(3, comment.getContent());

			pstmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
	}
	
	public void updateComment(BoardComment comment) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			
			String sql = 
				"UPDATE boardcomment " +
				"SET content = ?" +
				"WHERE commentno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, comment.getContent());
			pstmt.setInt(2, comment.getCommentNo());

			pstmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
	}
	
	public void deleteComment(int commentNo) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			
			String sql = 
				"DELETE FROM boardcomment " +				
				"WHERE commentno = ?";
			pstmt = conn.prepareStatement(sql);			
			pstmt.setInt(1, commentNo);

			pstmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
	}

}




